﻿
CREATE PROCEDURE [dbo].[TableTemplate]
(
	@SchemaName varchar(50),
	@NewTableName varchar(50)  
)
AS
DECLARE @strSQL as varchar(4000)
BEGIN

SET @SchemaName = ltrim(rtrim(@SchemaName))
SET @NewTableName = ltrim(rtrim(@NewTableName))

SELECT @strSQL = N'CREATE TABLE [' + @SchemaName + N'].[' + @NewTableName + N'](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[GId] [int] NULL,
	[Name] [varchar](100) COLLATE Cyrillic_General_CI_AS NOT NULL,
	[ShortName] [varchar](50) COLLATE Cyrillic_General_CI_AS NOT NULL,
	[Note] [varchar](250) COLLATE Cyrillic_General_CI_AS NULL,
	[MD] [datetime] NOT NULL DEFAULT (getdate()),
	[MW] [varchar](50) COLLATE Cyrillic_General_CI_AS NOT NULL DEFAULT (suser_sname()),
 CONSTRAINT [PK_' + @NewTableName + N'] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE UNIQUE NONCLUSTERED INDEX [IX_' + @NewTableName + N'_Name] ON [' + @SchemaName + N'].[' + @NewTableName + N'] 
(
	[Name] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, 
IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

CREATE UNIQUE NONCLUSTERED INDEX [IX_' + @NewTableName + N'_ShortName] ON [' + @SchemaName + N'].[' + @NewTableName + N'] 
(
	[ShortName] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, 
IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
'

EXEC(@strSQL)

END
